/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;


import DTO.AccountDTO;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author dienvm60347
 */
public class AccountDAO {
    public static String checkLogin(String username, String password){
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = connectData.makeConnect();
            String sql = "Select * from tbl_User where username=? and password=?";
            stm = con.prepareStatement(sql);
            password = parseMD5(password);
            stm.setString(1, username);
            stm.setString(2, password);
            rs = stm.executeQuery();
            if(rs.next()){
                return rs.getString("user_role");
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                if(stm!=null){
                    stm.close();
                }
                if(rs!=null){
                    rs.close();
                }
                if(con!=null){
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }
    public static List<AccountDTO>  getAllUser(){
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        List<AccountDTO> listuser = new ArrayList();
        
        try {
            con = connectData.makeConnect();
            String sql = "Select * from tbl_User";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            
            while(rs.next()){
                AccountDTO account = new AccountDTO();
                account.setUsername(rs.getString("username"));
                account.setPassword(rs.getString("password"));
                account.setFullname(rs.getString("fullname"));
                account.setEmail(rs.getString("email"));
                account.setBirthdate(rs.getString("birthdate"));
                account.setSex(rs.getString("sex"));
                account.setAddress(rs.getString("address"));
                account.setPhone(rs.getString("phone"));
                account.setRole(rs.getString("user_role"));
                listuser.add(account);
            }
            return listuser;
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                if(stm!=null){
                    stm.close();
                }
                if(rs!=null){
                    rs.close();
                }
                if(con!=null){
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }
    // parse password
    private static String parseMD5(String input){
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] messagedigest = md.digest(input.getBytes());
            BigInteger number = new BigInteger(1, messagedigest);
            String hashtext = number.toString(16);
            while(hashtext.length()<32){
                hashtext = "0"+hashtext;
            }
            return hashtext;
        } catch (NoSuchAlgorithmException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    // register account
    public static boolean regisAccount(String username, String password, String fullname
            , String email, String birthdate, String sex, String address, String phone){
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con= connectData.makeConnect();
            password = parseMD5(password);
            String sql = "insert into tbl_User (username, password, fullname, "
                    + "email, birthdate, sex, address, phone, user_role) values (N'" + username +
                    "',N'" + password + "',N'" + fullname + "',N'"  + email + "',N'" + birthdate + "',N'" +
                    sex + "',N'" + address + "',N'" + phone + "',N'user')";
            stm= con.prepareStatement(sql);
            
            stm.executeUpdate();
            return true;
        } catch (SQLException e) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, e);
        } finally{
            
                try {
                    if(con!=null){
                        con.close();
                    }if(stm!=null){
                        stm.close();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
        return false;
    }
    // reset password
    private static String randomPassword(){
        String result = "";
        String randomStr = "QWERTYUIOPASDFGHJKLMNBVCXZ1234567890";
        int leng = randomStr.length() -1;
        for(int i=0;i<16;i++){
            int temp = (int) ((Math.random()*leng)+0.1);
            result += randomStr.charAt(temp);
        }
        return result;
    }
    public static String checkUserEmail(String user, String email){
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = connectData.makeConnect();
            String sql = "Select * from tbl_User where username=? and email=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, user);
            stm.setString(2, email);
            rs = stm.executeQuery();
            if(rs.next()){
                return randomPassword();
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                if(stm!=null){
                    stm.close();
                }
                if(rs!=null){
                    rs.close();
                }
                if(con!=null){
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }
    public static void updatePass(String pass, String user){
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con= connectData.makeConnect();
            String sql = "update tbl_User set password=? where username=?";
            stm= con.prepareStatement(sql);
            pass = parseMD5(pass);
            stm.setString(1, pass);
            stm.setString(2, user);
            stm.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally{

                try {
                    if(con!=null){
                        con.close();
                    }if(stm!=null){
                        stm.close();
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
    }
}
